package com.sl.au.repository;

import java.util.Date;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import com.sl.au.entity.Contract;
import com.sl.au.entity.ContractCruxDate;
import com.sl.au.entity.ContractInfo;
import com.sl.au.entity.ContractPart;
import com.sl.au.entity.ContractPump;
import com.sl.au.entity.ContractRepair;
import com.sl.au.entity.DivideScale;
import com.sl.au.entity.QuoteDevice;
import com.sl.au.entity.QuoteReplacement;
import com.sl.au.entity.User;

/**
 * 
 * @author XH
 *
 */
@Transactional(readOnly = true)
public interface ContractRepository extends JpaRepository<Contract, String> {
	/**
	 * 根据查询条件查询实际发货时间、调试完成时间、验收完成时间三个时间
	 * @param contractId
	 * @return
	 */
	@Query("select c from ContractInfo c where c.id=? ")
	ContractInfo getDateThreeContractById(String contractId);
	/**
	 * 根据查询条件查询合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where (ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%') "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	Page<ContractInfo> getContractInfos(String key, Pageable pageable);

	/**
	 * 根据查询条件及部门查询合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.superior = ?2 "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常'")
	Page<ContractInfo> getContractInfos(String key, String superior, Pageable pageable);

	/**
	 * 根据查询条件及部门片区查询合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param areaName
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and (ci.superior=?2 and ci.areaName=?3) "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常'")
	Page<ContractInfo> getContractInfos(String key, String superior, String areaName, Pageable pageable);

	/**
	 * 根据查询条件查询当前操作人的合同数据
	 * 
	 * @param key
	 * @param userId
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.userId = ?2 and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	Page<ContractInfo> getContractInfosByUser(String key, String userId, Pageable pageable);

	/**
	 * 根据id查合同信息
	 * 
	 * @param id
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ci.id=?1")
	ContractInfo getContractInfoById(String id);

	/**
	 * 根据查询条件查询合同数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where (ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%')"
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	long getContractInfoCount(String key);

	/**
	 * 根据查询条件及部门查询合同数据条数
	 * 
	 * @param key
	 * @param superior
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.superior = ?2 "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常'")
	long getContractInfoCount(String key, String superior);

	/**
	 * 根据查询条件及部门片区查询合同数据条数
	 * 
	 * @param key
	 * @param superior
	 * @param areaName
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and (ci.superior=?2 and ci.areaName=?3) "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	long getContractInfoCount(String key, String superior, String areaName);

	/**
	 * 根据查询条件查询当前操作人的合同数据条数
	 * 
	 * @param key
	 * @param userId
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.userId = ?2 and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	long getContractInfoByUserCount(String key, String userId);

	/**
	 * 根据查询条件查询与当前客户相关的合同数据
	 * 
	 * @param key
	 * @param userId
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' ) and ci.customerId = ?2  and (ci.auditState!='未提交' and ci.auditState!='驳回')")
	Page<ContractInfo> getContractInfosByCustomerId(String key, String customerId, Pageable pageable);

	/**
	 * 根据查询条件查询与当前客户相关的合同条数
	 * 
	 * @param key
	 * @param userId
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' ) and ci.customerId = ?2 and (ci.auditState!='未提交' and ci.auditState!='驳回')")
	long getContractInfoByCustomerCount(String key, String customerId);

	/**
	 * 根据查询条件查询项目合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c where c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' order by c.createTime desc ")
	Page<Contract> getContracts(String key, Pageable pageable);

	/**
	 * 根据查询条件查询项目合同数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select count(c) from Contract c where c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' ")
	long getContractCount(String key);

	/**
	 * 获取当前合同关键点
	 * 
	 * @param contractId
	 * @param pageable
	 * @return
	 */
	@Query(" select ccd from ContractCruxDate ccd where ccd.contractID=?1 order by ccd.typeNum,ccd.executiveDate desc ")
	Page<ContractCruxDate> getContractCruxDates(String contractId, Pageable pageable);

	/**
	 * 获取当前合同关键点条数
	 * 
	 * @param contractId
	 * @param pageable
	 * @return
	 */
	@Query(" select count(ccd) from ContractCruxDate ccd where ccd.contractID=?1 ")
	long getContractCruxDateCount(String contractId);

	/**
	 * 获取当前合同所有关键点
	 * 
	 * @param contractId
	 * @param pageable
	 * @return
	 */
	@Query(" select ccd from ContractCruxDate ccd where ccd.contractID=?1 order by ccd.typeNum,ccd.executiveDate desc ")
	List<ContractCruxDate> getContractCruxDates(String contractId);

	/**
	 * 通过合同Id删除当前合同下的所有关键点数据
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("delete from ContractCruxDate ccd where ccd.contractID=?1 ")
	public void deleteCruxDatesByContractId(String contractId);

	/**
	 * 通过合同Id删除当前项目合同下的所有占比分配数据
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("delete from DivideScale ds where ds.contractID=?1 ")
	public void deleteDivideScalesByContractId(String contractId);

	/**
	 * 通过合同Id删除当前(项目、备泵、维修)合同下的所有合同细目泵
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("delete from QuoteDevice qd where qd.contractID=?1 ")
	public void deleteDevicesByContractId(String contractId);

	/**
	 * 通过合同Id删除当前(项目、备件、维修)合同下的所有合同细目配件
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("delete from QuoteReplacement qr where qr.contractID=?1 ")
	public void deleteReplacementsByContractId(String contractId);

	/**
	 * 通过合同Id删除细目关联表中与该合同相关的记录
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("delete from DetailRelevance dr where dr.contractID=?1 or dr.detailContractID=?1 ")
	public void deleteRelevancesByContractId(String contractId);

	/**
	 * 删除项目合同后将关联过它的泵的供方合同号置为null
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("update QuoteDevice qd set qd.supplierCode=null where qd.supplierCode in "
			+ "( select c.supplierCode from Contract c where c.id=?1 ) ")
	public void updateDeviceSupplierCodeByContractId(String contractId);

	/**
	 * 删除项目合同后将关联过它的配件的供方合同号置为null
	 * 
	 * @param contractId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("update QuoteReplacement qr set qr.supplierCode=null where qr.supplierCode in "
			+ "( select c.supplierCode from Contract c where c.id=?1 ) ")
	public void updateReplacementSupplierCodeByContractId(String contractId);

	/**
	 * 通过当前用户部门得到该部门下所有人员
	 * 
	 * @param superior
	 *            部门
	 * @return
	 */
	@Query("select u from User u where u.saleArea.superior=?1 ")
	List<User> getAllSuperiorMenBySuperior(String superior);

	/**
	 * 通过当前用户部门得到该部门下所有的部门-片区
	 * 
	 * @param superior
	 *            部门
	 * @return
	 */
	@Query("select sa.superiorArea from SaleArea sa where sa.superior=?1 and sa.isAbandon != 1 ")
	List<Object> getSuperiorAreasBySuperior(String superior);

	/**
	 * 通过当前用户部门得到该部门下所有的片区
	 * 
	 * @param superior
	 *            部门
	 * @return
	 */
	@Query("select sa.areaName from SaleArea sa where sa.superior=?1 and sa.isAbandon != 1 ")
	List<Object> getAreasBySuperior(String superior);

	/**
	 * 通过角色名得到该部门的责任人(处长、副处长、片区经理)
	 * 
	 * @param head
	 *            处长
	 * @param deputyHead
	 *            副处长
	 * @param manager
	 *            片区经理
	 * @return
	 */
	@Query("select a.user.empLogin.userName from Account a where a.role.name in ( ?1, ?2, ?3 ) "
			+ " and a.isEnabled = 1 order by a.role.name asc ")
	List<Object> getDutyOfficersByroleName(String head, String deputyHead, String manager);

	/**
	 * 通过角色名得到该角色的用户名
	 * 
	 * @param roleName
	 *            角色名
	 * @return
	 */
	@Query("select a.user.empLogin.userName from Account a where a.role.name=?1 and a.isEnabled = 1 ")
	List<Object> getUserNamesByroleName(String roleName);

	/**
	 * 除去该合同供方合同号也为supplierCode的条数
	 * 
	 * @param contractId
	 * @param supplierCode
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ci.contractId != ?1 and ci.supplierCode = ?2 ")
	int getContractInfoCountBySupplierCode(String id, String code);

	/**
	 * 得到该项目合同的所有占比划分金额
	 * 
	 * @param contractId
	 * @return
	 */
	@Query(" select ds.scaleDivide from DivideScale ds where ds.contractID = ?1 ")
	List<Object> getAllScaleDivide(String id);

	/**
	 * 根据contractId查询项目合同数据
	 * 
	 * @param id
	 * @return
	 */
	@Query("select c from Contract c where c.id=? ")
	Contract getContractById(String contractId);

	/**
	 * 根据contractId查询备件合同数据
	 * 
	 * @param id
	 * @return
	 */
	@Query("select c from ContractPart c where c.id=? ")
	ContractPart getContractPartById(String contractId);

	/**
	 * 根据contractId查询备泵合同数据
	 * 
	 * @param id
	 * @return
	 */
	@Query("select c from ContractPump c where c.id=? ")
	ContractPump getContractPumpById(String contractId);

	/**
	 * 根据contractId查询维修合同数据
	 * 
	 * @param id
	 * @return
	 */
	@Query("select c from ContractRepair c where c.id=? ")
	ContractRepair getContractRepairById(String contractId);

	/**
	 * 根据contractId查询项目合同数据
	 * 
	 * @param id
	 * @return
	 */
	@Query("select c from Contract c where c.supplierCode=? ")
	Contract getContractBySupplierCode(String supplierCode);

	/**
	 * 根据contractId查询项目合同的占比划分
	 * 
	 * @param id
	 * @return
	 */
	@Query("select ds from DivideScale ds where ds.contractID=? order by ds.createTime asc ")
	Page<DivideScale> getDivideScaleById(String contractId, Pageable pageable);

	/**
	 * 根据contractId查询项目合同的占比划分条数
	 * 
	 * @param id
	 * @return
	 */
	@Query("select count(ds) from DivideScale ds where ds.contractID=? ")
	long getContractDivideScaleCount(String contractId);

	/**
	 * 通过(备件、备泵、维修)合同Id得到细目已关联的合同
	 * 
	 * @param contractId
	 * @return
	 */
	@Query("select c from Contract c where c.id in "
			+ "( select dr.contractID from DetailRelevance dr where dr.detailContractID=?1 ) "
			+ "order by c.createTime desc ")
	Page<Contract> getRelevanceContractById(String contractId, Pageable pageable);

	/**
	 * 通过(备件、备泵、维修)合同Id得到细目已关联的合同条数
	 * 
	 * @param contractId
	 * @return
	 */
	@Query("select count(c) from Contract c where c.id in "
			+ "( select dr.contractID from DetailRelevance dr where dr.detailContractID=?1 ) ")
	long getRelevanceContractByIdCount(String contractId);

	/**
	 * 根据contractID查询当前(项目、备泵、维修)合同细目泵
	 * 
	 * @param contractID
	 * @return
	 */
	@Query("select qd from QuoteDevice qd where qd.contractID=?1 order by qd.createTime desc ")
	Page<QuoteDevice> getDeviceById(String contractID, Pageable pageable);

	/**
	 * 根据contractID查询当前(项目、备泵、维修)合同细目泵条数
	 * 
	 * @param contractID
	 * @return
	 */
	@Query("select count(qd) from QuoteDevice qd where qd.contractID=?1 ")
	long getDeviceCountById(String contractID);

	/**
	 * 根据contractID查询当前(项目、备件、维修)合同细目配件
	 * 
	 * @param contractID
	 * @return
	 */
	@Query("select qr from QuoteReplacement qr where qr.contractID=?1")
	Page<QuoteReplacement> getReplacementById(String contractID, Pageable pageable);

	/**
	 * 根据contractID查询当前(项目、备件、维修)合同细目配件条数
	 * 
	 * @param contractID
	 * @return
	 */
	@Query("select count(qr) from QuoteReplacement qr where qr.contractID=?1 ")
	long getReplacementCountById(String contractID);

	/**
	 * 通过细目Id删除细目关联表中与该细目相关的记录
	 * 
	 * @param detailId
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("delete from DetailRelevance dr where dr.detailID=?1 ")
	public void deleteRelevancesBydetailId(String detailId);

	/**
	 * 售后设备信息处合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c order by c.createTime desc ")
	Page<Contract> getAfterSaleContracts1(Pageable pageable);

	/**
	 * 售后设备信息处合同数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select count(c) from Contract c ")
	long getAfterSaleContractsCount1();

	/**
	 * 根据查询条件查询售后设备信息处合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c where ( c.supplierCode like '%'+?1+'%' or "
			+ "c.projectName like '%'+?1+'%' or c.orderUnit like '%'+?1+'%' or c.orderAddress like '%'+?1+'%' )"
			+ " order by c.createTime desc ")
	Page<Contract> getAfterSaleContracts2(String key, Pageable pageable);

	/**
	 * 根据查询条件查询售后设备信息处合同数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select count(c) from Contract c where ( c.supplierCode like '%'+?1+'%' or "
			+ "c.projectName like '%'+?1+'%' or c.orderUnit like '%'+?1+'%' or c.orderAddress like '%'+?1+'%' )")
	long getAfterSaleContractsCount2(String key);

	/**
	 * 得到所有可以被关联的项目合同的供方合同号
	 * 
	 * @return
	 */
	@Query("select c.supplierCode from Contract c where c.supplierCode is not null "
			+ "and c.supplierCode != '' order by c.createTime desc ")
	List<Object> getContractSupplierCodeList();

	/**
	 * 得到所有可以新建售后设备信息的项目合同的供方合同号
	 * 
	 * @return
	 */
	@Query("select c.supplierCode from Contract c where c.supplierCode is not null "
			+ "and c.supplierCode != '' and ( isAfterSale = false or isAfterSale is null )"
			+ " order by c.createTime desc ")
	List<Object> getContractSupplierCodeList1();

	/**
	 * 通过项目合同Id得到该合同以及其它关联该合同的细目
	 * 
	 * @param id
	 * @return
	 */
	@Query("select qd from QuoteDevice qd where ( qd.contractID=?1 or qd.id in "
			+ " ( select dr.detailID from DetailRelevance dr where dr.contractID=?1 ) ) "
			+ " order by qd.createTime desc ")
	List<QuoteDevice> getAllDetailById(String contractId);

	/**
	 * 根据contractId查询(项目、备泵、维修)合同细目
	 * 
	 * @param id
	 * @return
	 */
	@Query("select qd from QuoteDevice qd where qd.contractID=?1 order by qd.createTime desc ")
	List<QuoteDevice> getDetailById(String contractId);

	/**
	 * 根据查询条件查询项目合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c where c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' "
			+ "order by c.createTime desc ")
	List<Contract> getContracts(String key);

	/**
	 * 根据查询条件查询当前用户的项目合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c where c.user.id =?2 and ( c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' ) "
			+ "order by c.createTime desc ")
	List<Contract> getContracts(String key, String userId);

	/**
	 * 根据查询条件查询备件合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ContractPart c where c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' "
			+ "order by c.createTime desc ")
	List<ContractPart> getContractParts(String key);

	/**
	 * 根据查询条件查询当前用户的备件合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ContractPart c where c.user.id =?2 and ( c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' ) "
			+ "order by c.createTime desc ")
	List<ContractPart> getContractParts(String key, String userId);

	/**
	 * 根据查询条件查询备泵合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ContractPump c where c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' "
			+ "order by c.createTime desc ")
	List<ContractPump> getContractPumps(String key);

	/**
	 * 根据查询条件查询当前用户的备泵合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ContractPump c where c.user.id =?2 and ( c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' ) "
			+ "order by c.createTime desc ")
	List<ContractPump> getContractPumps(String key, String userId);

	/**
	 * 根据查询条件查询维修合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ContractRepair c where c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' "
			+ "order by c.createTime desc ")
	List<ContractRepair> getContractRepairs(String key);

	/**
	 * 根据查询条件查询当前用户的维修合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ContractRepair c where c.user.id =?2 and ( c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' or c.postUnit like '%'+?1+'%' or c.contractType like '%'+?1+'%' ) "
			+ "order by c.createTime desc ")
	List<ContractRepair> getContractRepairs(String key, String userId);

	/**
	 * 根据高级查询条件查询所有合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' ")
	Page<ContractInfo> getContractInfos(String supplierCode, String contractType, float minSum, float maxSum,
			Date startDate, Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2,
			String orderUnit, Pageable pageable);

	/**
	 * 根据高级查询条件查询当前部门合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' "
			+ " and ci.superior = ?12 ")
	Page<ContractInfo> getContractInfos(String supplierCode, String contractType, float minSum, float maxSum,
			Date startDate, Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2,
			String orderUnit, String superior, Pageable pageable);

	/**
	 * 根据高级查询条件查询当前部门当前片区合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param areaName
	 * @param pageable
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' "
			+ " and ci.superior = ?12 and ci.areaName = ?13 ")
	Page<ContractInfo> getContractInfos(String supplierCode, String contractType, float minSum, float maxSum,
			Date startDate, Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2,
			String orderUnit, String superior, String areaName, Pageable pageable);

	/**
	 * 根据高级查询条件查询当前用户合同数据
	 * 
	 * @param key
	 * @param userId
	 * @return
	 */
	@Query(" select ci from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' "
			+ " and ci.userId = ?12 ")
	Page<ContractInfo> getContractInfosByUser(String supplierCode, String contractType, float minSum, float maxSum,
			Date startDate, Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2,
			String orderUnit, String userId, Pageable pageable);

	/**
	 * 根据高级查询条件查询所有合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' ")
	long getContractInfoCount(String supplierCode, String contractType, float minSum, float maxSum, Date startDate,
			Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2, String orderUnit);

	/**
	 * 根据高级查询条件查询当前部门合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param pageable
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' "
			+ " and ci.superior = ?12 ")
	long getContractInfoCount(String supplierCode, String contractType, float minSum, float maxSum, Date startDate,
			Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2, String orderUnit,
			String superior);

	/**
	 * 根据高级查询条件查询当前部门当前片区合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param areaName
	 * @param pageable
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' "
			+ " and ci.superior = ?12 and ci.areaName = ?13 ")
	long getContractInfoCount(String supplierCode, String contractType, float minSum, float maxSum, Date startDate,
			Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2, String orderUnit,
			String superior, String areaName);

	/**
	 * 根据高级查询条件查询当前用户合同数据
	 * 
	 * @param key
	 * @param userId
	 * @param pageable
	 * @return
	 */
	@Query(" select count(ci) from ContractInfo ci where ci.supplierCode like '%'+?1+'%' and ci.contractType like '%'+?2+'%' "
			+ "and ci.totalSum >= ?3 and ci.totalSum <= ?4 and ci.signedDate >= ?5 and ci.signedDate <= ?6 and ci.deliveryDate >= ?7 "
			+ " and ci.deliveryDate <= ?8 and ci.deliveryDate >= ?9 and ci.deliveryDate <= ?10 and ci.orderUnit like '%'+?11+'%' "
			+ " and ci.userId = ?12 ")
	long getContractInfoByUserCount(String supplierCode, String contractType, float minSum, float maxSum,
			Date startDate, Date endDate, Date startDate1, Date endDate1, Date startDate2, Date endDate2,
			String orderUnit, String userId);
	
	/**
	 * 根据createTime时间获得新增的合同数量
	 */
	//按部门查看
	@Query(" select count(ci) from ContractInfo ci where ci.superior = ?1 and (ci.createTime >= ?2 and ci.createTime <= ?3)")
    long getSuperiorNewCCount(String superior,Date start,Date end);
	//按科长查看
	@Query(" select count(ci) from ContractInfo ci where ci.superior = ?1 and "
			+ "(ci.areaName in ( select s.saleArea.areaName from SectionArea s where s.section.id=?2 ) ) and (ci.createTime >= ?3 and ci.createTime <= ?4)")
	long getSectionNewCCount(String superior,String sectionId,Date start,Date end);
	
	//按片区经理查看
	@Query(" select count(ci) from ContractInfo ci where ci.superior = ?1 and "
			+ "ci.areaName = ?2 and (ci.createTime >= ?3 and ci.createTime <= ?4)")
	long getAreaNewCCount(String superior,String areaName,Date start,Date end);
	//按个人查看
	@Query(" select count(ci) from ContractInfo ci where ci.userId= ?1 and "
			+ "(ci.createTime >= ?2 and ci.createTime <= ?3)")
	long getMyNewCCount(String userId,Date start,Date end);
	//查看所有
	@Query(" select count(ci) from ContractInfo ci where ci.createTime >= ?1 and ci.createTime <= ?2")
	long getAllNewCCount(Date start,Date end);
	
	/*
	 * 时间范围内新增的客户，
	 * 客户的定义为在客户的所有合同都在这个时间范围里
	 */
	//看所有
	@Query("select count(*) from Customer c where c.id in (select ci.customerId from ContractInfo ci group by ci.customerId having max(ci.createTime)<=?2 "
			+ "and min(createTime)>=?1)")
	long getAllNewCustomer(Date start,Date end);
	
	//看本部门
	@Query("select count(*) from Customer c where c.user.saleArea.superior=?1 and c.id in (select ci.customerId from ContractInfo ci group by ci.customerId having max(ci.createTime)<=?3 "
			+ "and min(createTime)>=?2)")
	long getSuperiorNewCustomer(String superior,Date start,Date end);
	
	//看本科室	
	@Query("select count(*) from Customer c where (c.user.saleArea.id in ( select s.saleArea.id from SectionArea s where s.section.id=?1 )) "
			+ "and c.id in (select ci.customerId from ContractInfo ci group by ci.customerId having max(ci.createTime)<=?3 "
			+ "and min(createTime)>=?2)")
	long getSectionNewCustomer(String sectionId,Date start,Date end);
	
	//本片区
	@Query("select count(*) from Customer c where (c.user.saleArea.id=?1) "
			+ "and c.id in (select ci.customerId from ContractInfo ci group by ci.customerId having max(ci.createTime)<=?3 "
			+ "and min(createTime)>=?2)")
	long getAreaNewCustomer(String areaId,Date start,Date end);
	
	//本人的
	@Query("select count(*) from Customer c where (c.user.id=?1) "
			+ "and c.id in (select ci.customerId from ContractInfo ci group by ci.customerId having max(ci.createTime)<=?3 "
			+ "and min(createTime)>=?2)")
	long getMyNewCustomer(String userId,Date start,Date end);
}
